package xfsale.service.impl.j2ee;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

import xfsale.exception.AppException;
import xfsale.packet.TransAcctOpenRequest;
import xfsale.packet.TransAcctOpenResponse;

public final class TransAcctOpen extends BaseOrderAcct {

	private TransAcctOpenRequest request;
	private TransAcctOpenResponse response;

	private boolean custExist = false;
	private String custAccountIDExist;

	public TransAcctOpen(TransAcctOpenRequest request, TransAcctOpenResponse response) {
		super(request, response);
		this.request = request;
		this.response = response;
		response.returnCode = "0000";
		response.returnMsg = "交易账户开户成功";
	}

	//检查输入参数
    @Override
	protected void checkData() throws Exception {
		super.checkData();

		checkCustType(request.custType);
		checkCustName(request.custName);
		checkCustNameAbbr(request.custNameAbbr);
		checkNationality(request.nationality);
		checkCertType(request.certType);
		checkCertNo(request.certNo);
		checkBankCode(request.bankCode);
		checkBankName(request.bankName);
		checkBankAddress(request.bankAddress);
		checkBankAcctName(request.bankAcctName);
		checkBankAcctNo(request.bankAcctNo);
		checkBankAcctVerified(request.bankAcctVerified);
		checkAddress(request.address);
		checkPostCode(request.postCode);
		checkTelNo(request.telNo);
		checkHomeTelNo(request.homeTelNo);
		checkOfficeTelNo(request.officeTelNo);
		checkMobileTelNo(request.mobileTelNo);
		checkFaxNo(request.faxNo);
		checkEmail(request.email);
		checkTradingMethod(request.tradingMethod);
		checkTradePassword(request.tradePassword);
		checkDeliverType(request.deliverType);
		checkDeliverWay(request.deliverWay);
		checkSZSecAccountID(request.szSecAccountID);
		checkSHSecAccountID(request.shSecAccountID);
		checkSex(request.sex);
		checkBirthday(request.birthday);
		checkIncome(request.income);
		checkEducation(request.education);
		checkVocation(request.vocation);
		checkRiskTolerance(request.riskTolerance);

		//检查身份证合法性
		if (request.custType.equals("1") && request.certType.equals("0")) {
			rule.checkIdcard(request.certNo);
		}
	}

	//初始化
    @Override
	protected void load() throws Exception {
		super.load();
	}

	//检查功能权限
    @Override
	protected void checkPriv() throws Exception {
		super.checkPriv();
	}

	//检查条件是否满足
    @Override
	protected void checkCondition() throws Exception {
		super.checkCondition();

		//检查身份号码是否满足条件
		checkID();

		//检查银行信息是否满足条件
		checkBank();
	}

	//功能处理
    @Override
	protected void process() throws Exception {
		//填充并写入客户账户数据
		writeAcctCust();

		//填充并写入交易账户数据
		writeAcctTrans();

		//填充并写入银行账户数据
		writeAcctBank();

		//填充并写入经办人数据
		writeAcctTransactor();

		//填充并写入账户委托数据
		writeOrderAcct();
	}

	//检查证件号码是否满足条件
	private void checkID() throws Exception {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			//检查客户帐户表AccctCust中是否已经存在该证件
			stmt = conn.prepareStatement("" +
				"select count(*) " +
				"from AcctCust " +
				"where custType = ? " +
				"and certType = ? " +
				"and certNo = ?"
			);
			stmt.setString(1, request.custType);
			stmt.setString(2, request.certType);
			stmt.setString(3, request.certNo);
			rs = stmt.executeQuery();
			rs.next();
			custExist = rs.getInt(1) > 0;
			stmt.close();
			logger.debug("证件号码已存在");

			if (custExist) {
				stmt = conn.prepareStatement("" +
					"select custAccountID " +
					"from AcctCust " +
					"where custType = ? " +
					"and certType = ? " +
					"and certNo = ?"
				);
				stmt.setString(1, request.custType);
				stmt.setString(2, request.certType);
				stmt.setString(3, request.certNo);
				rs = stmt.executeQuery();
				rs.next();
				custAccountIDExist = rs.getString(1);
				stmt.close();
				logger.debug("客户帐户表AccctCust中是否已经存在该证件.");
			}

			//如果是个人投资者，检查客户账户表AcctCust中15位与18位身份证号码是否存在
			if (!custExist) {
				if (request.custType.equals("1") && request.certType.equals("0")) {
					if (request.certNo.length() == 18) {
						stmt = conn.prepareStatement("" +
							"select count(*) " +
							"from AcctCust " +
							"where custType = ? " +
							"and certType = ? " +
							"and certNo = substr(?, 1, 6) || substr(?, 9, 9)"
						);
						stmt.setString(1, request.custType);
						stmt.setString(2, request.certType);
						stmt.setString(3, request.certNo);
						stmt.setString(4, request.certNo);
						rs = stmt.executeQuery();
						rs.next();
						custExist = rs.getInt(1) > 0;
						stmt.close();

						if (custExist) {
							stmt = conn.prepareStatement("" +
								"select custAccountID " +
								"from AcctCust " +
								"where custType = ? " +
								"and certType = ? " +
								"and certNo = substr(?, 1, 6) || substr(?, 9, 9)"
							);
							stmt.setString(1, request.custType);
							stmt.setString(2, request.certType);
							stmt.setString(3, request.certNo);
							stmt.setString(4, request.certNo);
							rs = stmt.executeQuery();
							rs.next();
							custAccountIDExist = rs.getString(1);
							stmt.close();
						}
					}
					if (request.certNo.length() == 15) {
						stmt = conn.prepareStatement("" +
							"select count(*) " +
							"from AcctCust " +
							"where custType = ? " +
							"and certType = ? " +
							"and substr(certNo, 1, 6) || substr(certNo, 9, 9) = ?"
						);
						stmt.setString(1, request.custType);
						stmt.setString(2, request.certType);
						stmt.setString(3, request.certNo);
						rs = stmt.executeQuery();
						rs.next();
						custExist = rs.getInt(1) > 0;
						stmt.close();

						if (custExist) {
							stmt = conn.prepareStatement("" +
								"select custAccountID " +
								"from AcctCust " +
								"where custType = ? " +
								"and certType = ? " +
								"and substr(certNo, 1, 6) || substr(certNo, 9, 9) = ?"
							);
							stmt.setString(1, request.custType);
							stmt.setString(2, request.certType);
							stmt.setString(3, request.certNo);
							rs = stmt.executeQuery();
							rs.next();
							custAccountIDExist = rs.getString(1);
							stmt.close();
						}
					}
				}
			}

			//检查交易账户表AcctTrans中存在状态不为销户的交易帐号
			if (custExist) {
				stmt = conn.prepareStatement("" +
					"select count(*) " +
					"from AcctTrans " +
					"where custAccountID = ? " +
					"and transAcctStatus <> '1'"
				);
				stmt.setString(1, custAccountIDExist);
				rs = stmt.executeQuery();
				rs.next();
				custExist = rs.getInt(1) > 0;
				stmt.close();

				if (custExist) {
					throw new AppException("证件号码重复");
				}
			}
		} catch (Exception e) {
			logger.error("检查证件号码出错. " + e);
			throw e;
		} finally {
			stmt.close();
		}
	}

	//检查银行信息是否满足条件
	private void checkBank() throws Exception {
	}

	//写入客户账户数据
	private void writeAcctCust() throws Exception {
		//如果客户账户已经存在，直接返回
		if (custExist) {
			response.custAccountID = custAccountIDExist;
			return;
		}

		//生产客户帐号
		acct.genCustAccount();
		response.custAccountID = acct.getCustAccountID();

		//写入客户账户数据
		PreparedStatement stmt = null;
		try {
			logger.debug("写入客户账户数据...");
			stmt = conn.prepareStatement("" +
				"insert into AcctCust" +
				"(" +
				"	custAccountID, " +			//客户帐号
				"	custType, " +				//客户类型
				"	custName, " +				//客户名称
				"	custNameAbbr, " +			//客户简称
				"	nationality, " +			//国籍
				"	certType, " +				//证件类型
				"	certNo, " +					//证件号码
				"	certValidFrom, " +			//证件有效期起始日期
				"	certValidTo, " +			//证件有效期截至日期
				"	shSecAccountID, " +			//上交所证券帐号
				"	szSecAccountID, " +			//深交所证券帐号
				"	address, " +				//通讯地址
				"	postCode, " +				//邮政编码
				"	telNo, " +					//电话号码
				"	homeTelNo, " +				//住址电话
				"	officeTelNo, " +			//办公电话
				"	mobileTelNo, " +			//手机号码
				"	faxNo, " +					//传真号码
				"	email, " +					//E-MAIL地址
				"	birthday, " +				//出生日期
				"	sex, " +					//性别
				"	education, " +				//教育程度
				"	vocation, " +				//职业代码
				"	income, " +					//年收入
				"	instType, " +				//机构类型
				"	instReprName, " +			//法人姓名
				"	instReprCertType, " +		//法人证件类型
				"	instReprCertNo, " +			//法人证件号码
				"	instReprCertValidFrom, " +	//法人证件有效期起始日期
				"	instReprCertValidTo, " +	//法人证件有效期截至日期
				"	custAcctOpenDate, " +		//开户日期
				"	custAcctOpenTime, " +		//开户时间
				"	summary " +					//备注
				")" +
				"values " +
				"( " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	? " +
				")"
			);
			stmt.setString(1, response.custAccountID);				//客户帐号
			stmt.setString(2, request.custType);					//投资者类型
			stmt.setString(3, request.custName);					//投资者人名
			stmt.setString(4, request.custNameAbbr);					//投资者简称
			stmt.setString(5, request.nationality);					//投资者国籍
			stmt.setString(6, request.certType);					//投资者证件类型
			stmt.setString(7, request.certNo);						//投资者证件号码
			stmt.setString(8, request.certValidFrom);				//投资者证件有效期起始日期
			stmt.setString(9, request.certValidTo);					//投资者证件有效期截至日期
			stmt.setString(10, request.shSecAccountID);				//上交所证券帐号
			stmt.setString(11, request.szSecAccountID);				//深交所证券帐号
			stmt.setString(12, request.address);					//通讯地址
			stmt.setString(13, request.postCode);					//邮政编码
			stmt.setString(14, request.telNo);						//电话号码
			stmt.setString(15, request.homeTelNo);					//住址电话
			stmt.setString(16, request.officeTelNo);				//办公电话
			stmt.setString(17, request.mobileTelNo);				//手机号码
			stmt.setString(18, request.faxNo);						//传真号码
			stmt.setString(19, request.email);						//E-MAIL地址
			stmt.setString(20, request.birthday);					//出生日期
			stmt.setString(21, request.sex);						//性别
			stmt.setString(22, request.education);					//教育程度
			stmt.setString(23, request.vocation);					//职业代码
			stmt.setString(24, request.income);						//年收入
			stmt.setString(25, request.instType);					//机构类型
			stmt.setString(26, request.instReprName);				//法人姓名
			stmt.setString(27, request.instReprCertType);			//法人证件类型
			stmt.setString(28, request.instReprCertNo);				//法人证件号码
			stmt.setString(29, request.instReprCertValidFrom);		//法人证件有效期起始日期
			stmt.setString(30, request.instReprCertValidTo);		//法人证件有效期截至日期
			stmt.setString(31, sys.getWorkDay());					//开户日期
			stmt.setString(32, sys.getWorkTime());					//开户时间
			stmt.setString(33, null);								//备注
			stmt.executeUpdate();
			logger.debug("写入客户账户数据成功.");
		} catch (Exception e) {
			logger.error("写入客户账户数据出错. " + e);
			throw e;
		} finally {
			stmt.close();
		}
	}

	//写入交易账户数据
	private void writeAcctTrans() throws Exception {
		//生产交易帐号
		acct.setInvstType(request.custType);
		acct.genTransAccount();
		response.transAccountID = acct.getTransAccountID();

		//写入交易账户数据
		PreparedStatement stmt = null;
		try {
			logger.debug("写入交易账户数据...");
			stmt = conn.prepareStatement("" +
				"insert into AcctTrans" +
				"(" +
				"	transAccountID, " +				//交易帐号
				"	custAccountID, " +				//客户帐号
				"	regionCode, " +					//开户所在地区编号
				"	branchCode, " +					//开户所在网点编号
                "	netCode, " +					//开户所在销售网点编号
				"	deliverType, " +				//对帐单寄送选择
				"	deliverWay, " +					//对账单寄送方式
				"	tradingMethod, " +				//交易方式
				"	tradePassword, " +				//交易密码
				"	riskTolerance, " +				//风险承受能力
				"	tradePassErrTimes, " +			//交易密码错误次数
				"	transAcctStatus, " +			//交易账户状态
				"	transAcctOpenDate, " +			//开户日期
				"	transAcctOpenTime, " +			//开户时间
				"	summary" +						//备注
				")" +
				"values " +
				"( " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	? " +
				")"
			);
			stmt.setString(1, response.transAccountID);	//交易帐号
			stmt.setString(2, response.custAccountID);	//客户帐号
			stmt.setString(3, request.regionCode);		//开户所在地区编号
			stmt.setString(4, request.branchCode);		//开户所在网点编号
            stmt.setString(5, request.netCode);         //开户所在销售网点编号
			stmt.setString(6, request.deliverType);		//对帐单寄送选择
			stmt.setString(7, request.deliverWay);		//对账单寄送方式
			stmt.setString(8, request.tradingMethod);	//交易方式
			stmt.setString(9, request.tradePassword);	//交易密码
			stmt.setString(10, request.riskTolerance);	//风险承受能力
			stmt.setInt(11, 0);							//交易密码错误次数
			stmt.setString(12, "0");					//交易账户状态
			stmt.setString(13, sys.getWorkDay());		//开户日期
			stmt.setString(14, sys.getWorkTime());		//开户时间
			stmt.setString(15, null);					//备注
			stmt.executeUpdate();
			logger.debug("写入交易账户数据成功.");
		} catch (Exception e) {
			logger.error("写入交易账户数据出错. " + e);
			throw e;
		} finally {
			stmt.close();
		}
	}

	//写入银行账户数据
	private void writeAcctBank() throws Exception {
		//写入银行账户数据
		PreparedStatement stmt = null;
		try {
			logger.debug("写入银行账户数据...");
			stmt = conn.prepareStatement("" +
				"insert into AcctBank" +
				"(" +
				"	transAccountID, " +				//交易帐号
				"	bankAcctType, " +				//银行帐号类型
				"	bankCode, " +					//银行代码
				"	bankName, " +					//银行全称
				"	bankAcctName, " +				//银行帐号户名
				"	bankAcctNo, " +					//银行帐号卡号
				"	bankAcctVerified, " +			//银行帐号验证
				"	bankAcctStatus, " +				//银行帐号状态
				"	bankAcctOpenDate, " +			//开户日期
				"	bankAcctOpenTime, " +			//开户时间
				"	summary" +						//备注
				")" +
				"values " +
				"( " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	? " +
				")"
			);
			stmt.setString(1, response.transAccountID);		//交易帐号
			stmt.setString(2, "0");							//银行帐号类型
			stmt.setString(3, request.bankCode);			//银行代码
			stmt.setString(4, request.bankName);			//银行全称
			stmt.setString(5, request.bankAcctName);		//银行帐号户名
			stmt.setString(6, request.bankAcctNo);			//银行帐号卡号
			stmt.setString(7, request.bankAcctVerified);	//银行帐号验证
			stmt.setString(8, "0");							//银行帐号状态
			stmt.setString(9, sys.getWorkDay());			//开户日期
			stmt.setString(10, sys.getWorkTime());			//开户时间
			stmt.setString(11, null);						//备注
			stmt.executeUpdate();
			logger.debug("写入银行账户数据成功.");
		} catch (Exception e) {
			logger.error("写入银行账户数据出错. " + e);
			throw e;
		} finally {
			stmt.close();
		}
	}

	//写入经办人数据
	private void writeAcctTransactor() throws Exception {
		if (request.custType.equals("1"))
			return;

		//写入经办人数据
		PreparedStatement stmt = null;
		try {
			logger.debug("写入经办人数据...");
			stmt = conn.prepareStatement("" +
				"insert into AcctTransactor" +
				"(" +
				"	transAccountID, " +				//交易帐号
				"	instTransType, " +				//经办人类型
				"	instTransName, " +				//经办人姓名
				"	instTransCertType, " +			//经办人证件类型
				"	instTransCertNo, " +			//经办人证件号码
				"	instTransCertValidFrom, " +		//经办人证件有效期起始日期
				"	instTransCertValidTo, " +		//经办人证件有效期截至日期
				"	instTransTelNo, " +				//经办人联系电话
				"	instTransMobileTelNo, " +		//经办人移动电话
				"	summary" +						//备注
				")" +
				"values " +
				"( " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	?, " +
				"	? " +
				")"
			);
			stmt.setString(1, response.transAccountID);			//交易帐号
			stmt.setString(2, "0");								//经办人类型
			stmt.setString(3, request.instTransName);			//经办人姓名
			stmt.setString(4, request.instTransCertType);		//经办人证件类型
			stmt.setString(5, request.instTransCertNo);			//经办人证件号码
			stmt.setString(6, request.instTransCertValidFrom);	//经办人证件有效期起始日期
			stmt.setString(7, request.instTransCertValidTo);	//经办人证件有效期截至日期
			stmt.setString(8, request.instTransTelNo);			//经办人联系电话
			stmt.setString(9, request.instTransMobileTelNo);	//经办人移动电话
			stmt.setString(10, null);							//备注
			stmt.executeUpdate();
			logger.debug("写入经办人数据成功.");
		} catch (Exception e) {
			logger.error("写入经办人数据出错. " + e);
			throw e;
		} finally {
			stmt.close();
		}
	}

	//写入账户委托数据
	private void writeOrderAcct() throws Exception {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		try {
			//生成委托流水
			logger.debug("生成委托流水...");
			serial.setTradeDate(sys.getWorkDay());
			serial.genOrderSerial();
			response.orderSerialNo = serial.getOrderSerialNo();

			//写入账户委托数据
			logger.debug("写入账户委托数据...");
			stmt = conn.prepareStatement(SQL_WRITE_ORDER_ACCT);
			stmt.setString(1, response.orderSerialNo);		//委托流水
			stmt.setString(2, request.serviceCode);			//功能代码
            stmt.setString(3, businessCode);                //业务码
			stmt.setString(4, request.acceptMethod);		//受理方式
			stmt.setString(5, request.regionCode);			//地区编号
			stmt.setString(6, request.branchCode);			//网点编号
            stmt.setString(7, request.netCode);             //销售网点编号
			stmt.setString(8, request.opCode);				//柜员编号
			stmt.setString(9, response.custAccountID);		//客户帐号
			stmt.setString(10, response.transAccountID);	//交易帐号
			stmt.setString(11, null);						//基金帐号
			stmt.setString(12, null);						//TA代码
			stmt.setString(13, request.custType);			//客户类型
			stmt.setString(14, request.custName);			//客户名称
			stmt.setString(15, request.custNameAbbr);		//客户简称
			stmt.setString(16, request.nationality);		//国籍
			stmt.setString(17, request.certType);			//证件类型
			stmt.setString(18, request.certNo);				//证件号码
			stmt.setString(19, request.certValidFrom);		//证件有效期起始日期
			stmt.setString(20, request.certValidTo);		//证件有效期截至日期
			stmt.setString(21, request.shSecAccountID);		//上交所证券帐号
			stmt.setString(22, request.szSecAccountID);		//深交所证券帐号
			stmt.setString(23, request.address);			//通讯地址
			stmt.setString(24, request.postCode);			//邮政编码
			stmt.setString(25, request.telNo);				//电话号码
			stmt.setString(26, request.homeTelNo);			//住址电话
			stmt.setString(27, request.officeTelNo);		//办公电话
			stmt.setString(28, request.mobileTelNo);		//手机号码
			stmt.setString(29, request.faxNo);				//传真号码
			stmt.setString(30, request.email);				//E-MAIL地址
			stmt.setString(31, request.birthday);			//出生日期
			stmt.setString(32, request.sex);				//性别
			stmt.setString(33, request.education);			//教育程度
			stmt.setString(34, request.vocation);			//职业代码
            stmt.setString(35, request.industry);			//行业代码
            stmt.setString(36, request.marriage);			//婚姻状况
			stmt.setString(37, request.income);				//年收入
			stmt.setString(38, request.instType);			//机构类型
            stmt.setString(39, request.instNature);			//机构性质
            stmt.setString(40, request.instRegAddress);		//机构注册地
            stmt.setString(41, request.instRegCapital);		//机构注册资本
            stmt.setString(42, request.instEmployees);		//机构员工数量
			stmt.setString(43, request.instReprName);		//法人姓名
			stmt.setString(44, request.instReprCertType);	//法人证件类型
			stmt.setString(45, request.instReprCertNo);		//法人证件号码
			stmt.setString(46, request.instReprCertValidFrom);		//法人证件有效期起始日期
			stmt.setString(47, request.instReprCertValidTo);		//法人证件有效期截至日期
			stmt.setString(48, request.instTransName);				//经办人姓名
			stmt.setString(49, request.instTransCertType);			//经办人证件类型
			stmt.setString(50, request.instTransCertNo);			//经办人证件号码
			stmt.setString(51, request.instTransCertValidFrom);		//经办人证件有效期起始日期
			stmt.setString(52, request.instTransCertValidTo);		//经办人证件有效期截至日期
			stmt.setString(53, request.instTransTelNo);				//经办人联系电话
			stmt.setString(54, request.instTransMobileTelNo);		//经办人移动电话
            stmt.setString(55, request.broker);                     //经纪人
			stmt.setString(56, request.bankCode);					//银行代码
            stmt.setString(57, request.bankAddress);				//银行地址
			stmt.setString(58, request.bankName);					//银行全称
			stmt.setString(59, request.bankAcctName);				//银行帐号户名
			stmt.setString(60, request.bankAcctNo);					//银行帐号卡号
            stmt.setString(61, request.bankAcctExpired);            //银行账号有效期
			stmt.setString(62, request.bankAcctVerified);			//银行帐号验证
			stmt.setString(63, request.deliverType);				//对帐单寄送选择
			stmt.setString(64, request.deliverWay);					//对账单寄送方式
			stmt.setString(65, request.tradingMethod);				//交易方式
			stmt.setString(66, request.riskTolerance);				//风险承受能力
			stmt.setString(67, null);						//冻结截至日期
			stmt.setString(68, null);						//冻结原因
			stmt.setString(69, null);						//原委托流水
			stmt.setString(70, null);						//原申请流水
			stmt.setString(71, "0");						//委托预约标志
			stmt.setString(72, null);						//委托预约日期
			stmt.setString(73, "0");						//是否需要授权
			stmt.setString(74, null);						//授权标志(0-未授权 1-授权通过 2-授权不通过)
			stmt.setString(75, null);						//授权操作员
			stmt.setString(76, null);						//授权日期
			stmt.setString(77, null);						//授权时间
			stmt.setString(78, "1");						//委托状态
			stmt.setString(79, sys.getWorkDay());			//委托日期
			stmt.setString(80, sys.getWorkTime());			//委托时间
			stmt.setString(81, null);                       //备注
			stmt.executeUpdate();
			logger.debug("写入账户委托数据成功.");
		} catch (Exception e) {
			logger.error("写入账户委托数据出错. " + e);
			throw e;
		} finally {
			stmt.close();
		}
	}

}